package com.eduction.modules.system.dao;

import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eduction.common.dbutil.DbHelper;
import com.eduction.modules.system.entity.School;
import com.eduction.modules.system.entity.SchoolGroupMap;

@Repository
public class SiteInspectionSchoolGroupMapRepository {

	@Autowired
	private DbHelper dbhelper;
	@Autowired
	private SiteInspectionSchoolGroupMapDao siteInspectionSchoolGroupMapDao;
	
	/**
	 * 为专家组分配学校,添加映射（现场）
	 */
	public void setGroupToSchool(SchoolGroupMap s) throws SQLException{
		this.siteInspectionSchoolGroupMapDao.save(s);
		//修改学校绑定状态
		String sql = "update t_school set isband=1 where p_id=?";
		this.dbhelper.execute(sql, s.getS_schoolId());
	}

	/**
	 * 为专家组分配学校,添加映射（材料）
	 */
	public void setGroupToSchool2(SchoolGroupMap s) throws SQLException{
		this.siteInspectionSchoolGroupMapDao.save(s);
		//修改学校绑定状态
		String sql = "update t_school set isband2=1 where p_id=?";
		this.dbhelper.execute(sql, s.getS_schoolId());
	}
	
	/**
	 * 删除组与学校的关系（现场）
	 */
	public void deleteSchoolInGroup(String schoolId,String groupId) throws SQLException{
		String sql = "delete from t_school_group_map where s_school_id = ? and s_group_id = ?";
		this.dbhelper.execute(sql, schoolId,groupId);
		//解除学校绑定状态
		String sql1 = "update t_school set isband=0 where p_id=?";
		this.dbhelper.execute(sql1, schoolId);
	}

	/**
	 * 删除组与学校的关系（材料）
	 */
	public void deleteSchoolInGroup2(String schoolId,String groupId) throws SQLException{
		String sql = "delete from t_school_group_map where s_school_id = ? and s_group_id = ?";
		this.dbhelper.execute(sql, schoolId,groupId);
		//解除学校绑定状态
		String sql1 = "update t_school set isband2=0 where p_id=?";
		this.dbhelper.execute(sql1, schoolId);
	}
	/**
	 * 查询某个专家组下的学校
	 */
	public List<School> querySchoolByGroup(String groupId) throws SQLException{
		String sql = "SELECT t.p_id as p_id,t.s_name as s_name from t_school as t LEFT JOIN t_school_group_map as s on t.p_id=s.s_school_id "
				+" where s.s_group_id = ? ";
		return this.dbhelper.getBeanList(sql, School.class, groupId);
	}

	/**
	 * 删除专家组
	 * @param groupId
	 */
    public void deleteGroup(String groupId) throws SQLException {
		String sql = "delete from t_group where p_id = ?";
		this.dbhelper.execute(sql,groupId);
    }
}
